/**
 * 
 */
package com.aaron.server;

import com.aaron.client.Collaborator;
import com.aaron.client.Document;

import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.io.*;


/**
 * @author aaron
 *
 */
public class ProjectServer {
	private Connection con;
	public ProjectServer()throws DatabaseException{
		try{
			String username="aaron";
			String password="li!insin";
			String url = "jdbc:mysql://localhost/VivCode";
			Class.forName ("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection (url, username, password);
		}
		catch(ClassNotFoundException e){
			System.err.println ("ClassNotFoundException "+e.getMessage());
			throw new DatabaseException(e.getMessage());
		}
		catch(InstantiationException e){
			System.err.println ("InstantiationException "+e.getMessage());
			throw new DatabaseException(e.getMessage());
		}
		catch(IllegalAccessException e){
			System.err.println ("IllegalAccessException "+e.getMessage());
			throw new DatabaseException(e.getMessage());
		}
		catch(SQLException e){
			System.err.println ("SQLException "+e.getMessage());
			throw new DatabaseException(e.getMessage());
		}
		System.out.println ("Database connection established");
	}
	public void createProject(String project_name, String path, String username, String svn_url, String svn_username, String svn_password){
		try{
			Statement stmt = con.createStatement();
			String sql="INSERT INTO `Projects` ( `name` , `path` , `username` , `association` , `svn_url`, `svn_username`, `svn_password`, `last_editor` , `last_edited` )VALUES ('"+project_name+"', '"+path+"', '"+username+"', 'owner', '"+svn_url+"', '"+svn_username+"', '"+svn_password+"', 'me', NOW( ));";
			System.err.println ("createProject:sql:"+sql);
			stmt.executeUpdate(sql);
		}
		catch(SQLException e){
			System.err.println ("createProject:SQLException "+e.getMessage());
		}
	}
	public void deleteProject(String project_name, String username){
		try{
			String sql="DELETE FROM `Projects` WHERE `name` = '"+project_name+"' AND `username` = '"+username+"' LIMIT 1;";
			Statement stmt = con.createStatement();
			stmt.executeUpdate(sql);
			
		}
		catch(SQLException e){
			System.err.println ("deleteProject:SQLException "+e.getMessage());
		}
	}
	public void renameProject(String project_name, String new_name, String username){
		try{
			Statement stmt = con.createStatement();
			String sql="UPDATE `Projects` SET `name` = '"+new_name+"' WHERE `name`='"+project_name+"' AND `username`='"+username+"' LIMIT 1;";
			stmt.executeUpdate(sql);
			
		}
		catch(SQLException e){
			System.err.println ("renameProject:SQLException "+e.getMessage());
		}
	}
	public void updateLastEdit(String project_path, String username){
		try{
			Statement stmt = con.createStatement();
			String sql="UPDATE `Projects` SET `last_editor` = '"+username+"' `last_edit`=NOW() WHERE `path`='"+project_path+"';";
			stmt.executeUpdate(sql);
			
		}
		catch(SQLException e){
			System.err.println ("updateLastEdit:SQLException "+e.getMessage());
		}
	}
	public ArrayList getCollaborators(String path){
		ArrayList collaborators=new ArrayList();
		try{
			String sql="SELECT `username`, `association` FROM `Projects` WHERE `path` = '"+path+"';";
			System.err.println (sql);
			Statement stmt = con.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			while(rs.next()){
				collaborators.add(new Collaborator(rs.getString("username"), rs.getString("association")));
			}
		}
		catch(SQLException e){
			System.err.println ("getCollaborators:SQLException "+e.getMessage());
		}
		return collaborators;
	}
	public void setCollaborators(String project_path, ArrayList collaborators){
		try{
			//preserve the svn username and passwords for collaborators when collaborators are set
			
			//make a tuple containing all collaborators
			String names="(";
			Iterator itr=collaborators.iterator();
			while(itr.hasNext()){
				Collaborator collaborator=(Collaborator)itr.next();
				names+=" '"+collaborator.getUsername()+"' , ";
			}
			//omit last to chars ", "
			names=names.substring(0, names.length()-2);
			names+=")";
			//delete all collaborators and viewers whos names are not in the tuple
			String sql="DELETE FROM `Projects` WHERE `path` = '"+project_path+"' AND (`association`=\"collaborator\" OR `association`=\"viewer\") AND `username` NOT IN "+names+";";
			System.err.println ("setCollaborators: executing sql: "+sql);
			Statement stmt = con.createStatement();
			stmt.executeUpdate(sql);
			
			//get some data
			String project_name="";
			String svn_username="";
			String svn_password="";
			String svn_url="";
			sql="SELECT `name`, `svn_url` FROM `Projects` WHERE `path` = '"+project_path+"' LIMIT 1;";
			stmt = con.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			if(rs.next()){
				project_name=rs.getString("name");
				svn_url=rs.getString("svn_url");
			}
			
			//insert all collaborators and viewers into the project so that collaborators not already in the db will be added
			itr=collaborators.iterator();
			while(itr.hasNext()){
				Collaborator collaborator=(Collaborator)itr.next();
				String username=collaborator.getUsername();
				String association=collaborator.getAssociation();
				sql="INSERT INTO `Projects` ( `name` , `path` , `username` , `association` , `svn_url`, `svn_username`, `svn_password`, `last_editor` , `last_edited` )VALUES ('"+project_name+"', '"+project_path+"', '"+username+"', 'owner', '"+svn_url+"', '"+svn_username+"', '"+svn_password+"', 'me', NOW( )) ON DUPLICATE KEY UPDATE `association`='"+association+"';";
				stmt = con.createStatement();
				try{
					stmt.executeUpdate(sql);
				}
				catch(SQLException e){
					System.err.println ("getCollaborators-sub:SQLException "+e.getMessage());
				}
			}
		}
		catch(SQLException e){
			System.err.println ("getCollaborators:SQLException "+e.getMessage());
		}
	}
	public ArrayList listProjects(String auth){
		ArrayList projects=new ArrayList();
		try{
			SessionServer session_server=new SessionServer();
			String username=session_server.getUsername(auth);

			String sql="SELECT `name` FROM `Projects` WHERE `username` = '"+username+"' LIMIT 1;";
			Statement stmt = con.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			while(rs.next()){
				projects.add(rs.getString("name"));
			}
		}
		catch(SQLException e){
			System.err.println ("listProjects:SQLException "+e.getMessage());
		}
		catch(DatabaseException e){
			System.err.println ("listProjects:DatabaseException "+e.getMessage());
		}
		return projects;
	}
	public String getProjectStats(String project_path){
		String result="";
		// run the Unix "sloccount ./project-cache/project_path" command
        try{
            Process p = Runtime.getRuntime().exec("sloccount ./project-cache/"+project_path);
            
            BufferedReader stdInput = new BufferedReader(new 
                 InputStreamReader(p.getInputStream()));

            BufferedReader stdError = new BufferedReader(new 
                 InputStreamReader(p.getErrorStream()));

            // read the output from the command
            
            System.out.println("Here is the standard output of the command:\n");
			String s = null;
            while ((s = stdInput.readLine()) != null) {
                System.out.println(s);
				result+=s+"\n";
            }
            
            //format output
            result=result.split("(Computing results\\.)|(SLOCCount, Copyright)")[1];
            result=result.trim();
            result+="generated using David A. Wheeler's 'SLOCCount'.";
            //result=result.replaceAll("\n", "<br/>");
            result="<pre>"+result+"</pre>";
            
            // read any errors from the attempted command

            System.out.println("Here is the standard error of the command (if any):\n");
            while ((s = stdError.readLine()) != null) {
                System.out.println(s);
            }
        }
        catch (IOException e) {
            System.out.println("exception happened - here's what I know: ");
            e.printStackTrace();
        }
		return result;
	}
	public String getProjectName(Document document){
		try{
			String path=getProjectPath(document.getPath());
			String sql="SELECT `name` FROM `Projects` WHERE `path` = '"+path+"' LIMIT 1;";
			Statement stmt = con.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			if(rs.next()){
				return rs.getString("name");
			}
		}
		catch(SQLException e){
			System.err.println ("getProjectName:SQLException "+e.getMessage());
		}
		return "";
	}
	public String getSvnUsername(String project_name, String username){
		try{
			String sql="SELECT `svn_username` FROM `Projects` WHERE `username` = '"+username+"' AND `name`='"+project_name+"' LIMIT 1;";
			Statement stmt = con.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			if(rs.next()){
				return rs.getString("svn_username");
			}
		}
		catch(SQLException e){
			System.err.println ("getSvnUsername:SQLException "+e.getMessage());
		}
		return "";
	}
	public String getSvnPassword(String project_name, String username){
		try{
			String sql="SELECT `svn_password` FROM `Projects` WHERE `username` = '"+username+"' AND `name`='"+project_name+"' LIMIT 1;";
			Statement stmt = con.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			if(rs.next()){
				return rs.getString("svn_password");
			}
		}
		catch(SQLException e){
			System.err.println ("getSvnPassword:SQLException "+e.getMessage());
		}
		return "";
	}
	public String getSvnUrl(String project_name, String username){
		try{
			String sql="SELECT `svn_url` FROM `Projects` WHERE `username` = '"+username+"' AND `name`='"+project_name+"' LIMIT 1;";
			Statement stmt = con.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			if(rs.next()){
				return rs.getString("svn_url");
			}
		}
		catch(SQLException e){
			System.err.println ("getSvnUrl:SQLException "+e.getMessage());
		}
		return "";
	}
	public String getProjectPath(String project_name, String username){
		try{
			String sql="SELECT `path` FROM `Projects` WHERE `username` = '"+username+"' AND `name`='"+project_name+"' LIMIT 1;";
			Statement stmt = con.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			if(rs.next()){
				return rs.getString("path");
			}
		}
		catch(SQLException e){
			System.err.println ("getProjectPath:SQLException "+e.getMessage());
		}
		return "";
	}
	private static String getProjectPath(String document_path){
		String[] dirs=document_path.split("/");
		for(int i=0; i<dirs.length; i++){
			if(dirs[i].matches("[0-9a-f]{40}")){
				return dirs[i];
			}
		}
		return "";
	}
}
